package com.supermap.dataservice.cloud.adv;

import com.supermap.dubbo.api.cloud.adv.AdcCloudConment;
import com.supermap.dubbo.api.cloud.adv.AdvCloud;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 咨询
 * @date 2018年11月17 21:47
 * @email 1301584033@qq.com
 */
@Mapper
public interface AdvisoryCloudMpper {

    /***
     * 查看角色
     * */
    @Select(" select t1.showName,t3.code as roleCode,t3.name as roleName from  \n" +
            "            (select id,showName from t_user where id =#{userId}) t1\n" +
            "            inner join t_user_role t2\n" +
            "            on t1.id=t2.userid\n" +
            "            inner join t_role t3\n" +
            "            on t2.roleid=t3.id")
    Map<String,Object> getUserRoleByuserId(@Param("userId")String userId);

    /**
     *
     * */
    @Select("select group_concat(distinct advInfoId) from t_advinfocomment where find_in_set(id,#{commentId})")
    String getAdvIdByAdvCommentId(@Param("commentId")String commentId);
    /**
     * 查询
     * */
    @Select("select id,date_format(uploadTime,'%Y-%m-%d %H:%i:%S')  as dateTime from t_advisoryinfo where id=#{advId} ")
    Map<String,Object> getAdbById(@Param("advId")String advId);

    /**
     *
     * */
    @Select("select userId from t_expertinfo t1\n" +
            "inner join t_user t2\n" +
            "on t1.userid = t2.id and (t2.areaCode=#{areaCode} or t2.areaCode=50)\n" +
            "inner join t_expert_subject t3\n" +
            "on t1.id=t3.expertId  inner join t_subject t4\n" +
            "            on t3.subjectId =t4.id  and t4.code=#{subjectId}")
    List<String> getExpertUserIdByareacodeAndSubjectId(@Param("areaCode")String areaCode,@Param("subjectId")String subjectId);
    /**
     *收藏
     * */
    @Insert("insert into t_advcollect(userId,advId) values(#{userId},#{advId});")
    int insertAdvisoryCollect(@Param("userId")String userId,@Param("advId")String advId);


    /**
     * 取消收藏
     * **/
    @Delete("delete from t_advcollect where userId=#{userId} and advId=#{advId}")
    int delAdvisoryCollect(@Param("userId")String userId,@Param("advId")String advId);

    /**
     *点赞
     * */
    @Insert("insert into t_advpraise(userId,advInfoId) values(#{userId},#{advId});")
    int insertAdvisoryPraise(@Param("userId")String userId,@Param("advId")String advId);

    /**
     * 取消点赞
     * **/
    @Delete("delete from t_advpraise where userId=#{userId} and advInfoId=#{advId}")
    int delAdvisoryPraise(@Param("userId")String userId,@Param("advId")String advId);

    /**
     * 获取ID
     * */
    @Select("select Last_insert_id();")
    int getInsertAdvisoryId();
    /**
     * 上传图片
     * */
    @Insert("insert into t_advinfoimg(advInfoId,imageURLContent) values(#{advId},#{img});")
    int insertAdvisoryImg(@Param("advId")int advId,@Param("img")String img);

    @Select("select userId from t_advinfocomment where id=#{parentId}")
    String getAdvUserIdByParentId(@Param("parentId")String parentId);

    @Select("select userId from t_advisoryinfo where id=#{advId}")
    String getAdvUserIdByAdvId(@Param("advId")String advId);

    /**
     * 咨询上传
     * */
    @InsertProvider(type = AdvisoryCloudMpperSQL.class,method = "insertAdvisorySQL")
    int insertAdvisory(@Param("AdvCloud") AdvCloud advCloud);

    /**
     * 咨询评论上传
     * */
    @InsertProvider(type = AdvisoryCloudMpperSQL.class,method = "insertAdvisoryConmentSQL")
    int insertAdvinfocomment(@Param("adcCloudConment") AdcCloudConment adcCloudConment);

    /**
     * 查看咨询列表
     */
    @SelectProvider(type =AdvisoryCloudMpperSQL.class,method = "getPageAdvListSQL")
    List<AdvList> getPageAdvList(@Param("areaCode")String areaCode,@Param("userId")String userId,@Param("farmId")String farmId,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSise,@Param("ak")String ak,@Param("loginName")String loginName,@Param("advId")String advId,@Param("expertUserId")String expertUserId,@Param("keyName")String keyName);

    @SelectProvider(type =AdvisoryCloudMpperSQL.class,method = "getPageAdvListCountSQL")
    int getPageAdvListCount(@Param("areaCode")String areaCode,@Param("userId")String userId,@Param("farmId")String farmId,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSise,@Param("ak")String ak,@Param("loginName")String loginName,@Param("advId")String advId,@Param("expertUserId")String expertUserId,@Param("keyName")String keyName);
    /**
     * 获取学科
     * */

    @SelectProvider(type =AdvisoryCloudMpperSQL.class,method = "getSubjectSQL")
    List<Map<String,Object>> getSubject(@Param("userId")String userId);

    /***
     * 查看更多评论
     */
    @Select("select \n" +
            "t1.id,\n" +
            "t1.advInfoId,\n" +
            "t1.`comment`,\n" +
            "t1.parentId,\n" +
            "t1.userId,\n" +
            "t2.showName,\n" +
            "t2.HeadImage,\n" +
            "date_format(t1.commentTime,'%Y-%m-%d %H:%i:%S') as commentTime \n" +
            "from\tt_advinfocomment t1 \n" +
            "  Left join t_user t2  on t1.userId=t2.id "+
            "where t1.advInfoId=#{advId} and t1.parentId=#{parentId}\n" +
            "order by t1.commentTime desc\n" +
            "limit #{pageNum},#{pageSize}")
    List<AdvComment> getPageAdvConmment(@Param("advId")String advId,@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize,@Param("parentId")Integer parentId);

    class  AdvisoryCloudMpperSQL{

        public String getSubjectSQL(Map<String,Object> map){
            StringBuilder stringBuilder = new StringBuilder();
            String userId=(String)map.get("userId");
            if(userId==null){
                stringBuilder.append("select t.code,t.name from t_subject t where t.del_flg=0 ");
            }else{
                stringBuilder.append("\t\t\t\tselect t3.code,t3.name from \n" +
                        "\t\t\t\t\t(select id from t_expertinfo where userid="+userId+" and del_flg=0 )  t1\n" +
                        "\t\t\t\t\tinner join t_expert_subject t2\n" +
                        "\t\t\t\t\ton  t1.id =t2.expertId\n" +
                        "\t\t\t\t\tinner join t_subject t3\n" +
                        "\t\t\t\t\ton t2.subjectId=t3.id");
            }
            return  stringBuilder.toString();
        }
        public String insertAdvisorySQL(Map<String,Object> map) {
            AdvCloud advCloud=(AdvCloud)map.get("AdvCloud");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("insert into t_advisoryinfo");
            StringBuilder title = new StringBuilder();
            StringBuilder val = new StringBuilder();
            if (advCloud.getUserId() != null) {
                title.append("userId,");
                val.append("'" + advCloud.getUserId() + "',");
            }
            if (advCloud.getAreacode() != null) {
                title.append("areacode,");
                val.append("'" + advCloud.getAreacode() + "',");
            }
            if (advCloud.getLat() != null) {
                title.append("lat,");
                val.append("'" + advCloud.getLat() + "',");
            }
            if (advCloud.getLon() != null) {
                title.append("lon,");
                val.append("'" + advCloud.getLon() + "',");
            }

            if (advCloud.getGrowthperiod() != null) {
                title.append("growthperiod,");
                val.append("'" + advCloud.getGrowthperiod() + "',");
            }
            if (advCloud.getType() != null) {
                title.append("type,");
                val.append("'" + advCloud.getType() + "',");
            }
            if (advCloud.getBread() != null) {
                title.append("breed,");
                val.append("'" + advCloud.getBread() + "',");
            }

            if (advCloud.getQuestion() != null) {
                title.append("question,");
                val.append("'" + advCloud.getQuestion() + "',");
            }
            if (advCloud.getSubjectid() != null) {
                title.append("subjectid,");
                val.append("'" + advCloud.getSubjectid() + "',");
            }
            if (advCloud.getDisastertype() != null) {
                title.append("disastertype,");
                val.append("'" + advCloud.getDisastertype() + "',");
            }
            if (advCloud.getFarmlandid() != null) {
                title.append("farmlandid,");
                val.append("'" + advCloud.getFarmlandid() + "',");
            }
            if (advCloud.getExpertId() != null) {
                title.append("expertId,");
                val.append("'" + advCloud.getExpertId() + "',");
            }
          return   stringBuilder.append("(" + title.substring(0, title.lastIndexOf(",")) + ") values(" + val.substring(0, val.lastIndexOf(",")) + ")").toString();
        }


        public String getPageAdvListSQL(Map<String,Object> map){
            StringBuilder stringBuilder=new StringBuilder();
            String areaCode=(String)map.get("areaCode");
            String keyName=(String)map.get("keyName");
            String userId=(String)map.get("userId");
            String farmId=(String)map.get("farmId");
            int pageNum=(int)map.get("pageNum");
            int pageSize=(int)map.get("pageSize");
            String ak=(String)map.get("ak");
            String advId=(String)map.get("advId");
            String loginName=(String)map.get("loginName");
            String expertUserId=(String)map.get("expertUserId");
            stringBuilder.append("select \n" +
                    "t1.id as advId,\n" +
                    "t2.id as userId,t100.code as subjectCode,t100.name as subjectName,\n" +
                    "t2.showName as userShowName,\n" +
                    "t2.HeadImage as userImg,\n" +
                    "t2.state as userState,\n" +
                    "t1.farmlandid as farmId,\n" +
                    "t3.farmname as farmName,\n" +
                    "t1.areacode as advCode,\n" +
                    "t4.areaname as advCodeName,\n" +
                    "t1.question as advquestion,\n" +
                    "t5.code as disaCode,\n" +
                    "t5.name as disaName,\n" +
                    "t1.`type`,\n" +
                    "t7.name as leve1Name,\n" +
                    "t1.breed,\n" +
                    "t8.name as leve2Name,\n" +
                    "t1.lat ,\n" +
                    "t1.lon,\n" +
                    "t6.id as pubertyId,\n" +
                    "t6.puberty as pubertyName,\n" +
                    "group_concat(distinct t9.imageURLContent) as advImgs,\n" +
                    "(select count(1) from t_advcollect where advId=t1.id and userId="+userId+" ) as userIsadvcollect,\n" +
                    "(select count(1) from t_advpraise where advInfoId=t1.id and userId="+userId+" ) as userIsadvpraise,\n" +
                    "(select count(1) from t_advcollect where advId=t1.id) as conllectSumNum,\n" +
                    "(select count(1) from t_advpraise where advInfoId=t1.id) as praiseSumNum,\n" +
                    "date_format(t1.uploadTime,'%Y-%m-%d %H:%i:%S') as createtime\n" +
                    " from  t_advisoryinfo t1\n" +
                    "inner join t_user t2\n" +
                    "on t1.userId=t2.id and t2.del_flg=0 ");

            if(areaCode!=null){
                stringBuilder.append(" and  t1.areacode like concat("+areaCode+",'%')" );
            }
            if(ak==null){
                //我的列表
                if(userId!=null){
                    stringBuilder.append(" and t1.userId="+userId+" ");
                }

            }
            if(advId!=null){
                stringBuilder.append(" and t1.id="+advId+" ");
            }
            if(loginName!=null){
                stringBuilder.append(" and t2.loginName="+loginName+" ");
            }
            if(farmId!=null){
                stringBuilder.append(" and t1.farmlandid="+farmId+" ");
            }
            if(expertUserId!=null){
                stringBuilder.append(" and find_in_set(t1.id,"+expertUserId+") ");
            }

            stringBuilder.append(" Left join t_farmland t3\n" +
                    "on t1.farmlandid =t3.id\n" +
                    "Left join t_area t4\n" +
                    "on t1.areacode=t4.areacode\n" +
                    "Left join t_disastertype t5\n" +
                    "on t1.disastertype=t5.code\n" +
                    "Left join  t_puberty t6\n" +
                    "on t1.growthperiod=t6.id\n" +
                    "Left join  t_crop t7\n" +
                    "on t1.`type`=t7.id\n" +
                    "Left join  t_crop t8\n" +
                    "on t1.breed=t8.id  \n" +
                    "Left join t_advinfoimg t9\n" +
                    "on t1.id=t9.advInfoId ");
            stringBuilder.append("  Left join  t_subject t100 on t1.subjectid=t100.code ");
            if(keyName!=null){
                stringBuilder.append(" where (t1.question like concat('%','"+keyName+"','%')  or  " +
                        " t100.name like concat('%','"+keyName+"','%') or  " +
                        //作物一级
                        " t7.name like concat('%','"+keyName+"','%')  or " +
                        //作物二级
                        " t8.name like concat('%','"+keyName+"','%') " +
                        " ) ");
            }
            if(ak==null||ak.equals("3")){
                //全查
                stringBuilder.append(" group by t1.id\n" +
                        "order by t1.uploadTime desc ");
            }else if(ak.equals("1")){
                //只查收藏
                stringBuilder.append(" inner join (select * from t_advcollect t10 where t10.userId="+userId+") t10\n" +
                        "on t1.id=t10.advId\n" +
                        "Left join (select * from t_advpraise t11 where t11.userId="+userId+") t11\n" +
                        "on t1.id=t11.advInfoId\n" +
                        "group by t1.id\n" +
                        "order by t1.uploadTime desc ");
            }else if(ak.equals("2")){
                //只查点赞的列表
                stringBuilder.append(" Left join (select * from t_advcollect t10 where t10.userId="+userId+") t10\n" +
                        "on t1.id=t10.advId\n" +
                        "inner join (select * from t_advpraise t11 where t11.userId="+userId+") t11\n" +
                        "on t1.id=t11.advInfoId\n" +
                        "group by t1.id\n" +
                        "order by t1.uploadTime desc ");
            }

            stringBuilder.append(" limit "+pageNum+","+pageSize+" ");
            return  stringBuilder.toString();
        }

        public String getPageAdvListCountSQL(Map<String,Object> map){
            StringBuilder stringBuilder=new StringBuilder();
            String keyName=(String)map.get("keyName");
            String areaCode=(String)map.get("areaCode");
            String userId=(String)map.get("userId");
            String farmId=(String)map.get("farmId");
            String expertUserId=(String)map.get("expertUserId");
            int pageNum=(int)map.get("pageNum");
            int pageSize=(int)map.get("pageSize");
            String ak=(String)map.get("ak");
            String advId=(String)map.get("advId");
            String loginName=(String)map.get("loginName");
            stringBuilder.append("select count(t1.id) from (");
            stringBuilder.append("select \n" +
                    "t1.id\n" +
                    " from  t_advisoryinfo t1\n" +
                    "inner join t_user t2\n" +
                    "on t1.userId=t2.id and t2.del_flg=0 ");


            if(areaCode!=null){
                stringBuilder.append(" and  t1.areacode like concat("+areaCode+",'%')" );
            }
            if(ak==null){
                //我的列表
                if(userId!=null){
                    stringBuilder.append(" and t1.userId="+userId+" ");
                }
            }
            if(advId!=null){
                stringBuilder.append(" and t1.id="+advId+" ");
            }
            if(loginName!=null){
                stringBuilder.append(" and t2.loginName="+loginName+" ");
            }
            if(farmId!=null){
                stringBuilder.append(" and t1.farmlandid="+farmId+" ");
            }
            if(expertUserId!=null){
                stringBuilder.append(" and find_in_set(t1.id,"+expertUserId+") ");
            }

            stringBuilder.append(" Left join t_farmland t3\n" +
                    "on t1.farmlandid =t3.id\n" +
                    "Left join t_area t4\n" +
                    "on t1.areacode=t4.areacode\n" +
                    "Left join t_disastertype t5\n" +
                    "on t1.disastertype=t5.code\n" +
                    "Left join  t_puberty t6\n" +
                    "on t1.growthperiod=t6.id\n" +
                    "Left join  t_crop t7\n" +
                    "on t1.`type`=t7.id\n" +
                    "Left join  t_crop t8\n" +
                    "on t1.breed=t8.id  \n" +
                    "Left join t_advinfoimg t9\n" +
                    "on t1.id=t9.advInfoId ");

            stringBuilder.append("  Left join  t_subject t100 on t1.subjectid=t100.code ");
            if(keyName!=null){
                stringBuilder.append(" where (t1.question like concat('%','"+keyName+"','%')  or  " +
                        " t100.name like concat('%','"+keyName+"','%') or  " +
                        //作物一级
                        " t7.name like concat('%','"+keyName+"','%')  or " +
                        //作物二级
                        " t8.name like concat('%','"+keyName+"','%') " +
                        " ) ");
            }
            if(ak==null||ak.equals("3")){
                //全查
                stringBuilder.append("group by t1.id\n" +
                        "order by t1.uploadTime desc ");
            }else if(ak.equals("1")){
                //只查收藏
                stringBuilder.append(" inner join (select * from t_advcollect t10 where t10.userId="+userId+") t10\n" +
                        "on t1.id=t10.advId\n" +
                        "Left join (select * from t_advpraise t11 where t11.userId="+userId+") t11\n" +
                        "on t1.id=t11.advInfoId\n" +
                        "group by t1.id\n" +
                        "order by t1.uploadTime desc ");
            }else if(ak.equals("2")){
                //只查点赞的列表
                stringBuilder.append(" Left join (select * from t_advcollect t10 where t10.userId="+userId+") t10\n" +
                        "on t1.id=t10.advId\n" +
                        "inner join (select * from t_advpraise t11 where t11.userId="+userId+") t11\n" +
                        "on t1.id=t11.advInfoId\n" +
                        "order by t1.uploadTime desc ");
            }
            stringBuilder.append(")t1");

            return  stringBuilder.toString();
        }

        public String insertAdvisoryConmentSQL(Map<String,Object> map) {
            AdcCloudConment adcCloudConment=(AdcCloudConment)map.get("adcCloudConment");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("insert into t_advinfocomment");
            StringBuilder title = new StringBuilder();
            StringBuilder val = new StringBuilder();
            if (adcCloudConment.getAdvInfoId() != null) {
                title.append("advInfoId,");
                val.append("'" + adcCloudConment.getAdvInfoId() + "',");
            }
            if (adcCloudConment.getComment() != null) {
                title.append("comment,");
                val.append("'" + adcCloudConment.getComment() + "',");
            }
            if (adcCloudConment.getParentId() != null) {
                title.append("parentId,");
                val.append("'" + adcCloudConment.getParentId() + "',");
            }
            if (adcCloudConment.getUserId() != null) {
                title.append("userId,");
                val.append("'" + adcCloudConment.getUserId() + "',");
            }
            return   stringBuilder.append("(" + title.substring(0, title.lastIndexOf(",")) + ") values(" + val.substring(0, val.lastIndexOf(",")) + ")").toString();
        }

    }
}
